![]() |
![]() |
Let us first open the query, select
the last parameter and click on the Edit… button. This will open the
previously described MDX query parameter dialog with all fields set to the
currently defined values. Now, change the substitution mode to Replace this instance from dynamically generated
list from the MDX command, and the new (additional) Substitution tab on this dialog becomes
visible:
Please note that the Accept button is disabled now, because
we have not defined all required fields for the substitution list yet. Let us
now open the Substitution
tab:
This tab has five (5) fields that
must be supplied during the parameter definition:
1.
Use this connect string field
(expects the entire valid connect-string that will be used to establish a
connection to the OLAP server)
2.
Target database
name
3.
MDX command to be
executed
4.
Display this type of
information, and
5.
Replace with this type of
information
Use this connect string field expects the
entire valid connect-string that will be used to establish a connection to the
OLAP server. If you know how to build a valid connect-string, just type it in
the field. However, you will most probably want to use the Build… button instead. When you click
on it, it opens the standard logon dialog:
All you need to do now is define
your connection parameters here. Please note that the server name on this dialog
is not editable, so if you want to change it, you will have to manually edit
this value later. In this example, we shall leave all parameters unchanged, and
the following connect-string is automatically generated for
us:
Provider=MSOLAP;Integrated Security=SSPI;Persist Security Info=False;Large Level Threshold=1000;Execution Location=1;Data Source=ARIAN-NEW;Connect Timeout=60;Writeback Timeout=60;Secured Cell Value=5;
This string looks a little bit
confusing, so let us separate the individual elements for better
clarity:
Provider=MSOLAP;
Integrated Security=SSPI;
Persist Security
Info=False;
Large Level Threshold=1000;
Execution Location=1;
Data
Source=ARIAN-NEW;
Connect Timeout=60;
Writeback Timeout=60;
Secured
Cell Value=5;
As you can see, for the selected
combination of connection parameters, the integrated connect-string builder has
generated nine (9) separate items. Obviously, other combinations may result in
different connection strings.
If you need to make any manual
changes (such as the target server name, for example) to this connect string,
you can edit it directly in the corresponding field.
Target database name field expects you to
type the correct database name that contains the cube on which the MDX command
is to be executed. In this example, we shall enter the value FoodMart 2000 for our database
name.
MDX command to be executed field expects
you to type a valid MDX command that will be executed on the target cube. This
command should contain only one defined axis, COLUMNS or AXIS(0) because we are building a list of individual items
from the returned results. Let us enter this simple command
here:
SELECT
[Measures].[MeasuresLevel].MEMBERS
ON AXIS(0)
FROM
[Sales]
In this MDX command, we are
returning summary values (on the entire cube) for all existing measures.
However, it is not yet clear what is supposed to be substituted and what should
the substitution values be. This is the purpose of the last two combo
boxes.
The two (2) bottom combo-boxes
allow you to specifically define the values for:
1.
Displayed value (Display this type of information
dropdown list), and
2.
Substitution value (Replace with this type of information
dropdown list)
Displayed value will be presented to the
end user and you should type some user-friendly, easy to understand
string.
Substitution value will be used in the
replacement process. When the end-user selects some item from this list, the
default parameter value will be replaced with the substitution value of the
selected item.
Both combos contain the following
thirteen (13) items:
1.
Dimension unique
name
2.
Dimension
caption
3.
Hierarchy unique
name
4.
Hierarchy
caption
5.
Level unique
name
6.
Level
caption
7.
Member unique
name
8.
Member
caption
9.
Measure unique
name
10. Measure
caption
11. Member property unique
name
12. Member property
value
13. Cell value
Except for the cell value, you
would typically set the display value to some caption, and the substitution
value to the unique name of some cube structure element. In our example, we
shall select the Measure caption
item for the displayed value, and the substitution value will be automatically
set to Measure unique
name!
When you have defined all these
parameters, you must test your definition before it can be accepted. A Test button serves exactly this
purpose. When you click it, it will try to open a new connection (using the
supplied connection string) and actually execute the provided MDX statement on
the selected database and cube. If the test fails, the Accept button will stay disabled.
Otherwise, it will become enabled and you will be allowed to change the entire
parameter definition.
We can now accept the predefined
list by clicking on the Accept
button. This will close the dialog and the change is automatically reflected in
the MDX queries
dialog:
More: